﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Logger;

namespace Tools
{
    public class Utility
    {
        public static DataTable schema_collections(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name], '' schemat ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   s.name [Name], sch.name schemat ");
                    sb.AppendLine("FROM  sys.xml_schema_collections s ");
                    sb.AppendLine("LEFT JOIN sys.schemas sch on s.schema_id = sch.schema_id");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }

            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable authorizations(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.database_principals ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable execute_as(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT 'SELF' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT 'OWNER' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.database_principals ");
                    sb.AppendLine("where type = 'U' ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable proceudres(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   '['+r.routine_catalog+'].['+r.routine_schema+'].['+r.routine_name+']' [Name] ");
                    sb.AppendLine("FROM  information_schema.routines r ");
                    sb.AppendLine("left join information_schema.parameters p on r.specific_name = p.specific_name ");
                    sb.AppendLine("where routine_type = 'PROCEDURE' and p.specific_name is null ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable queues(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   '['+ sch.name+'].['+s.name+']' [Name] ");
                    sb.AppendLine("FROM  sys.service_queues s ");
                    sb.AppendLine("LEFT JOIN sys.schemas sch on s.schema_id = sch.schema_id");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable queues_to_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   '['+ sch.name+'].['+s.name+']' [Name] ");
                    sb.AppendLine("FROM  sys.service_queues s ");
                    sb.AppendLine("LEFT JOIN sys.schemas sch on s.schema_id = sch.schema_id");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable contracts(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.service_contracts ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable contracts_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.service_contracts ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable contracts_to_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' AS Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.service_contracts ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable filegroups(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT 'DEFAULT' [Name] ");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.filegroups ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable message_types(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' AS Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   s.name ");
                    sb.AppendLine("FROM sys.service_message_types s ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];

                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable message_types_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   s.name ");
                    sb.AppendLine("FROM sys.service_message_types s ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];

                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static string queue(DbContext dbContext, string service)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   '['+ sch.name+'].['+sq.name+']' [Name] ");
                    sb.AppendLine("from sys.service_queue_usages squ");
                    sb.AppendLine("left join sys.services s on squ.service_id = s.service_id");
                    sb.AppendLine("left join sys.service_queues sq on squ.service_queue_id = sq.object_id");
                    sb.AppendLine("LEFT JOIN sys.schemas sch on sq.schema_id = sch.schema_id");
                    sb.AppendLine("where s.name = @name");

                    SqlCommand SelectCommand = new SqlCommand();
                    SelectCommand = new SqlCommand();
                    SelectCommand.CommandText = sb.ToString();
                    SelectCommand.CommandTimeout = 0;
                    SelectCommand.CommandType = CommandType.Text;
                    SelectCommand.Connection = connection;
                    SelectCommand.Parameters.AddWithValue("@name", service);

                    string res = (string)SelectCommand.ExecuteScalar();

                    connection.Close();

                    return res;
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static string xml_schema(DbContext dbContext, string principal, string schema_collection)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("select xml_schema_namespace(@dbo,@SchemaName) as text");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Parameters.AddWithValue("@dbo", principal);
                    da.SelectCommand.Parameters.AddWithValue("@SchemaName", schema_collection);
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        return ds.Tables[0].Rows[0].ItemArray[0].ToString();
                    }
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return string.Empty;
        }

        public static DataTable assemblies(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.assemblies ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable conversation_groups(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT null AS Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   conversation_group_id [Name] ");
                    sb.AppendLine("FROM  sys.conversation_groups ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable conversations(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   Upper(cast(conversation_handle as nvarchar(max))) [Name] ");
                    sb.AppendLine("FROM  sys.conversation_endpoints ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable services(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   s.name [Name]");
                    sb.AppendLine("FROM sys.services s ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable services_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   s.name [Name]");
                    sb.AppendLine("FROM sys.services s ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];

                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable certificates(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' AS Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.certificates ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable master_certificates(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' AS Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  master.sys.certificates ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable certificates_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("FROM  sys.certificates ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable message_types_to_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' AS 'Name'");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   c.name [Name] ");
                    sb.AppendLine("FROM sys.service_message_types c ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable contracts_in_service(DbContext dbContext, string service)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   sc.name [Name] ");
                    sb.AppendLine("from sys.service_contract_usages scu");
                    sb.AppendLine("left join sys.services s on scu.service_id = s.service_id");
                    sb.AppendLine("left join sys.service_contracts sc on scu.service_contract_id = sc.service_contract_id");
                    sb.AppendLine("where s.name = @service");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;
                    da.SelectCommand.Parameters.AddWithValue("@service", service);

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable contracts_not_service(DbContext dbContext, string service)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   sc.name [Name] ");
                    sb.AppendLine("from sys.service_contracts sc");
                    sb.AppendLine("where sc.service_contract_id not in (select sc.service_contract_id from sys.service_contract_usages scu");
                    sb.AppendLine("left join sys.services s on scu.service_id = s.service_id");
                    sb.AppendLine("left join sys.service_contracts sc on scu.service_contract_id = sc.service_contract_id");
                    sb.AppendLine("where s.name = @service)");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;
                    da.SelectCommand.Parameters.AddWithValue("@service", service);

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable message_types_to(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   c.name [Name] ");
                    sb.AppendLine("FROM sys.service_message_types c ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable routes(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   r.name [Name] ");
                    sb.AppendLine("FROM sys.routes r ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable routes_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   r.name [Name] ");
                    sb.AppendLine("FROM sys.routes r ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable endpoints(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   e.name [Name] ");
                    sb.AppendLine("FROM sys.endpoints e ");
                    sb.AppendLine("WHERE e.type_desc = 'SERVICE_BROKER'");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable endpoints_select(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   e.name [Name] ");
                    sb.AppendLine("FROM sys.endpoints e ");
                    sb.AppendLine("WHERE e.type_desc = 'SERVICE_BROKER'");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable principals(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   e.name [Name] ");
                    sb.AppendLine("FROM sys.database_principals e ");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable logins(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT   e.name [Name] ");
                    sb.AppendLine("FROM sys.database_principals e ");
                    sb.AppendLine("where e.type = 'S'");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable permissions(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   distinct permission_name [Name] ");
                    sb.AppendLine("from sys.fn_builtin_permissions(null) order by 1");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable remote_bindings(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("from sys.remote_service_bindings");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable schemas(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   name [Name] ");
                    sb.AppendLine("from sys.schemas");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable notifications(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [master];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   type_name [Name] ");
                    sb.AppendLine("from sys.event_notification_event_types ");
                    sb.AppendLine("where type_name not in ('QUEUE_ACTIVATION','BROKER_QUEUE_DISABLED')");
                    sb.AppendLine("order by 1");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static DataTable broker_notifications(DbContext dbContext)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT 'BROKER_QUEUE_DISABLED' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT 'QUEUE_ACTIVATION' Name");
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = new SqlCommand();
                    da.SelectCommand.CommandText = sb.ToString();
                    da.SelectCommand.CommandTimeout = 0;
                    da.SelectCommand.CommandType = CommandType.Text;
                    da.SelectCommand.Connection = connection;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    connection.Close();

                    return ds.Tables[0];
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return null;
        }

        public static void EnableDisableBroker(DbContext dbContext, bool enabled)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.Append("ALTER DATABASE [" + dbContext.Database + "]");
                    if (enabled)
                    {
                        sb.Append(" SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE");
                    }
                    else
                    {
                        sb.Append(" SET DISABLE_BROKER");
                    }

                    SqlCommand command = new SqlCommand();
                    command.CommandText = sb.ToString();
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.Text;
                    command.Connection = connection;
                    command.ExecuteNonQuery();

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }
        }

        public static bool is_xml_validation(DbContext dbContext, string name)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(UtilitySqlTools.Current.ConnectionString()))
                {
                    StringBuilder sb = new StringBuilder();

                    sb.AppendLine("USE [" + dbContext.Database + "];");
                    sb.AppendLine("SELECT '' Name");
                    sb.AppendLine("UNION ALL");
                    sb.AppendLine("SELECT   validation ");
                    sb.AppendLine("from sys.service_message_types");
                    sb.AppendLine("where name = @name");
                    SqlCommand SelectCommand = new SqlCommand();
                    SelectCommand = new SqlCommand();
                    SelectCommand.CommandText = sb.ToString();
                    SelectCommand.CommandTimeout = 0;
                    SelectCommand.CommandType = CommandType.Text;
                    SelectCommand.Connection = connection;
                    SelectCommand.Parameters.AddWithValue("@name", name);

                    SqlDataReader dr = SelectCommand.ExecuteReader();

                    string valid = "";

                    if (dr.HasRows)
                    {
                        dr.Read();

                        if (!dr.IsDBNull(0))
                            valid = dr.GetString(0);
                    }

                    dr.Close();

                    connection.Close();

                    return valid == "X";
                }
            }
            catch (Exception exception)
            {
                LoggerWriter.Current.WriteError(exception.Message);
            }

            return false;
        }

        public static List<string> event_group()
        {
            List<string> group = new List<string>();

            group.Add("");
            group.Add("DLL_EVENTS");
            group.Add("DDL_SERVER_LEVEL_EVENTS");
            group.Add("DDL_LINKED_SERVER_EVENTS");
            group.Add("DDL_LINKED_SERVER_LOGIN_EVENTS");
            group.Add("DDL_REMOTE_SERVER_EVENTS");
            group.Add("DDL_EXTENDED_PROCEDURE_EVENTS");
            group.Add("DDL_MESSAGE_EVENTS");
            group.Add("DDL_ENDPOINT_EVENTS");
            group.Add("DDL_SERVER_SECURITY_EVENTS");
            group.Add("DDL_LOGIN_EVENTS");
            group.Add("DDL_GDR_SERVER_EVENTS");
            group.Add("DDL_AUTHORIZATION_SERVER_EVENTS");
            group.Add("DDL_DATABASE_LEVEL_EVENTS");
            group.Add("DDL_FULLTEXT_CATALOG_EVENTS");
            group.Add("DDL_DEFAULT_EVENTS");
            group.Add("DDL_EXTENDED_PROPERTY_EVENTS");
            group.Add("DDL_PLAN_GUID_EVENTS");
            group.Add("DDL_RULE_EVENTS");
            group.Add("DDL_TABLE_VIEW_EVENTS");
            group.Add("DDL_TABLE_EVENTS");
            group.Add("DDL_VIEW_EVENTS");
            group.Add("DDL_INDEX_EVENTS");
            group.Add("DDL_STATISITICS_EVENTS");
            group.Add("DDL_SYNONYM_EVENTS");
            group.Add("DDL_FUNCTION_EVENTS");
            group.Add("DDL_PROCEDURE_EVENTS");
            group.Add("DDL_TRIGGER_EVENTS");
            group.Add("DDL_EVENT_NOTIFICATION_EVENTS");
            group.Add("DDL_ASSEMBLY_EVENTS");
            group.Add("DDL_TYPE_EVENTS");
            group.Add("DDL_DATABASE_SECURITY_EVENTS");
            group.Add("DDL_CERIFICATE_EVENTS");
            group.Add("DDL_USER_EVENTS");
            group.Add("DDL_ROLE_EVENTS");
            group.Add("DDL_APPLICATION_ROLE_EVENTS");
            group.Add("DDL_SCHEMA_EVENTS");
            group.Add("DDL_GDR_DATABASE_EVENTS");
            group.Add("DDL_AUTHORIZATION_DATABASE_EVENTS");
            group.Add("DDL_SSB_EVENTS");
            group.Add("DDL_MESSAGE_TYPE_EVENTS");
            group.Add("DDL_CONTRACT_EVENTS");
            group.Add("DDL_QUEUE_EVENTS");
            group.Add("DDL_SERVICE_EVENTS");
            group.Add("DDL_ROUTE_EVENTS");
            group.Add("DDL_REMOTE_SERVICE_BINDING_EVENTS");
            group.Add("DDL_XML_SCHEMA_COLLECTION_EVENTS");
            group.Add("DDL_PARTITION_EVENTS");
            group.Add("DDL_PARTITION_FUNCTION_EVENTS");
            group.Add("DDL_PARTITION_SCHEME_EVENTS");

            return group;
        }
    }
}
